IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[GetCOIUsersForDisclosure]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.GetCOIUsersForDisclosure
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/***************************************************************                            
* Name:   [GetCOIUsersForDisclosure]                            
* Purpose:  Retrieves the COI Users matching the filter parameters passed in using multiple table joins                             
* @admPersonId      Domain User ID (REQUIRED)                                      
* @firstName      FirstName of the user.                                    
* @lastName       LastName for the user.
* @supervisorFirstName  Supervisor firstName                               
* @supervisorLastName  Supervisor last name                                    
* @userName       UserName for the user.                                    
*********************************************************************/                            
CREATE PROCEDURE [dbo].[GetCOIUsersForDisclosure]                                
(                                
@admPersonId int,                                
@firstName  VARCHAR(64)=null,                                
@lastName VARCHAR(64)=null,
@supervisorFirstName  VARCHAR(64)=null,                               
@supervisorLastName  VARCHAR(64)=null,   
@userName VARCHAR(36)=null               
)                                
AS BEGIN                                
	SET NOCOUNT ON                                

	DECLARE @bSupervisorSelected BIT
	DECLARE @bUserSelected BIT
	DECLARE @bBothSelected BIT

	IF((@supervisorFirstName IS NULL) AND (@supervisorLastName IS NULL))
		SET @bSupervisorSelected = 0
	ELSE
		SET @bSupervisorSelected = 1

	IF((@firstName IS NULL) AND (@lastName IS NULL) AND (@userName IS NULL))
		SET @bUserSelected = 0
	ELSE
		SET @bUserSelected = 1
	
	IF ((@bSupervisorSelected = 1) AND (@bUserSelected = 1))
		SET @bBothSelected = 1

	IF(@firstName is null)                                
		SET @firstName='%'                                
	ELSE
		SET @firstName=@firstName+'%'                                 
	                        
	IF(@lastName is null)                                
		SET @lastName='%'                                
	ELSE                                
		SET @lastName=@lastName+'%' 

	IF(@supervisorFirstName is null)                                
		SET @supervisorFirstName='%'                                
	ELSE                                
		SET @supervisorFirstName=@supervisorFirstName+'%'                                 
	                        
	IF(@supervisorLastName is null)                                
		SET @supervisorLastName='%'                                
	ELSE                                
		SET @supervisorLastName=@supervisorLastName+'%'                                
	                        
	IF(@userName is null)                                
		SET @userName='%'                                
	ELSE                                
		SET @userName=@userName+'%' 
	
	DECLARE @@tempCOIUsersByName Table(ID int,Name varchar(100))
	
--	START of search logic
    IF (@bBothSelected = 1)
	BEGIN
		SELECT adp.id,adp.LastName + ',' + adp.FirstName as Name FROM admPeRson adp 
		INNER JOIN ConflictOfInterest coi ON adp.id = coi.admPersonId 
			AND ISNULL(CAST(coi.[Id] as varchar(10)),'*') =(SELECT ISNULL(CAST(MAX(Id)AS VARCHAR(10)),'%') AS LatestCOIId FROM conflictofinterest WHERE admPersonId = adp.Id AND Status <> 'COISTATUS_TERMINATED')            
		LEFT OUTER JOIN fwkDomainUser fdu ON adp.fwkDomainUserId = fdu.Id
		WHERE
			ISNULL(adp.[firstName], '*') like @firstName                                
			AND                                
			ISNULL(adp.[lastName], '*') like @lastName                                
			AND                                
			ISNULL(fdu.[userName], '*') like @userName
		UNION
		   (SELECT adp.id,adp.LastName + ',' + adp.FirstName as Name FROM admPerson adp
			WHERE
				adp.id IN (SELECT ChildId FROM COI_Hierarchy_Map
							WHERE
								ParentId IN (SELECT adp.id FROM admPeRson adp 
												INNER JOIN ConflictOfInterest coi ON adp.id = coi.admPersonId 
												AND ISNULL(CAST(coi.[Id] as varchar(10)),'*') =(SELECT ISNULL(CAST(MAX(Id)AS VARCHAR(10)),'%') AS LatestCOIId FROM conflictofinterest WHERE admPersonId = adp.Id AND Status <> 'COISTATUS_TERMINATED')            
												LEFT OUTER JOIN fwkDomainUser fdu ON adp.fwkDomainUserId = fdu.Id
												WHERE
												ISNULL(adp.[firstName], '*') like @supervisorFirstName                                
												AND                                
												ISNULL(adp.[lastName], '*') like @supervisorLastName)
								AND
								Removed = 0)
			UNION
			SELECT adp.id,adp.LastName + ',' + adp.FirstName as Name FROM admPeRson adp 
			INNER JOIN ConflictOfInterest coi ON adp.id = coi.admPersonId 
				AND ISNULL(CAST(coi.[Id] as varchar(10)),'*') =(SELECT ISNULL(CAST(MAX(Id)AS VARCHAR(10)),'%') AS LatestCOIId FROM conflictofinterest WHERE admPersonId = adp.Id AND Status <> 'COISTATUS_TERMINATED')            
			LEFT OUTER JOIN fwkDomainUser fdu ON adp.fwkDomainUserId = fdu.Id
			WHERE
				ISNULL(adp.[firstName], '*') like @supervisorFirstName                                
				AND                                
				ISNULL(adp.[lastName], '*') like @supervisorLastName)  
		ORDER BY NAME
	END
	ELSE IF (@bUserSelected = 1)--User has not passed Supervisor search criteria
	BEGIN
		SELECT adp.id,adp.LastName + ',' + adp.FirstName as Name FROM admPerson adp 
		INNER JOIN ConflictOfInterest coi ON adp.id = coi.admPersonId 
			AND ISNULL(CAST(coi.[Id] as varchar(10)),'*') =(SELECT ISNULL(CAST(MAX(Id)AS VARCHAR(10)),'%') AS LatestCOIId FROM conflictofinterest WHERE admPersonId = adp.Id AND Status <> 'COISTATUS_TERMINATED')            
		LEFT OUTER JOIN fwkDomainUser fdu ON adp.fwkDomainUserId = fdu.Id
		WHERE
			ISNULL(adp.[firstName], '*') like @firstName                                
			AND                                
			ISNULL(adp.[lastName], '*') like @lastName                                
			AND                                
			ISNULL(fdu.[userName], '*') like @userName
		ORDER BY Name
	END
	ELSE IF (@bSupervisorSelected = 1)
	BEGIN
		SELECT adp.id,adp.LastName + ',' + adp.FirstName as Name FROM admPeRson adp 
		INNER JOIN ConflictOfInterest coi ON adp.id = coi.admPersonId 
			AND ISNULL(CAST(coi.[Id] as varchar(10)),'*') =(SELECT ISNULL(CAST(MAX(Id)AS VARCHAR(10)),'%') AS LatestCOIId FROM conflictofinterest WHERE admPersonId = adp.Id AND Status <> 'COISTATUS_TERMINATED')            
		LEFT OUTER JOIN fwkDomainUser fdu ON adp.fwkDomainUserId = fdu.Id
		WHERE
			ISNULL(adp.[firstName], '*') like @supervisorFirstName                                
			AND                                
			ISNULL(adp.[lastName], '*') like @supervisorLastName                                
		UNION
		SELECT adp.id,adp.LastName + ',' + adp.FirstName as Name FROM admPerson adp
		WHERE
			adp.id IN (SELECT ChildId FROM COI_Hierarchy_Map
						WHERE
							ParentId IN (SELECT adp.id FROM admPeRson adp 
											INNER JOIN ConflictOfInterest coi ON adp.id = coi.admPersonId 
											AND ISNULL(CAST(coi.[Id] as varchar(10)),'*') =(SELECT ISNULL(CAST(MAX(Id)AS VARCHAR(10)),'%') AS LatestCOIId FROM conflictofinterest WHERE admPersonId = adp.Id AND Status <> 'COISTATUS_TERMINATED')            
											LEFT OUTER JOIN fwkDomainUser fdu ON adp.fwkDomainUserId = fdu.Id
											WHERE
											ISNULL(adp.[firstName], '*') like @supervisorFirstName                                
											AND                                
											ISNULL(adp.[lastName], '*') like @supervisorLastName)
							AND
							Removed = 0)
		Order by Name
	END
END                 


